﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;

namespace Transcend.DP3.Helper
{
	public class OleExcelReader : IDisposable
	{

		/// <summary>
		/// 自动判断ole连接字符串，仅支持office2003,office2007其他版本未经测试
		/// </summary>
		public OleExcelReader(string location) : this(location, null) { }

		/// <summary>
		/// 自定义oledb连接字符串，连接字符串格式：Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=NO;\"
		/// </summary>
		public OleExcelReader(string location, string connectionString)
		{
			// 检查文件是否存在
			if (File.Exists(Location)) new ArgumentException(string.Format("文件{0}不存在", Location));

			Location = location;

			ConnectionString = string.IsNullOrEmpty(connectionString) ? MkConnectionString(location) : string.Format(connectionString.Replace(";HDR=YES", ";HDR=NO"), location);

			Connection = new OleDbConnection(ConnectionString);
			Connection.Open();
		}

		/// <summary>
		/// excel文件位置
		/// </summary>
		public string Location { get; private set; }

		/// <summary>
		/// 取得连接字符串
		/// </summary>
		public string ConnectionString { get; private set; }

		public OleDbConnection Connection { get; set; }

		/// <summary>
		/// 取得工作表中的工作簿
		/// </summary>
		public StringCollection GetSheets(bool incEmpty = true)
		{

			StringCollection sheets = new StringCollection();
			DataTable dt = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
			var shName = string.Empty;
			foreach (DataRow r in dt.Rows)
			{
				shName = r["table_name"].ToString();
				if (!incEmpty)
				{
					if (!IsEmptykSheet(shName)) sheets.Add(shName);
				}
				else
				{
					sheets.Add(shName);
				}
			}

			return sheets;
		}

		/// <summary>
		/// 取得指定工作簿的数据
		/// </summary>
		public DataTable GetSheetData(string shName)
		{

			string cmdText = string.Format("select * from [{0}]", GetSheetName(shName));
			var dt = new DataTable();
			dt.TableName = shName;
			using (var cmd = new OleDbCommand(cmdText, Connection))
			{
				using (var adpt = new OleDbDataAdapter(cmd))
				{
					adpt.Fill(dt);
				}
			}

			return dt;
		}

		/// <summary>
		/// 取得工作簿中的列名
		/// </summary>
		public StringCollection GetSheetCols(string shName)
		{

			var columns = new StringCollection();
			var dt = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, shName, null });
			foreach (DataRow r in dt.Rows)
			{
				columns.Add(r["column_name"].ToString());
			}

			return columns;
		}

		/// <summary>
		/// 返回工作簿行数
		/// </summary>
		public int GetSheetRows(string shName)
		{
			var rows = 0;
			using (var cmd = Connection.CreateCommand())
			{
				cmd.CommandText = string.Format("select count(*) from [{0}]", shName);
				rows = Convert.ToInt32(cmd.ExecuteScalar());
			}
			return rows;
		}

		/// <summary>
		/// 检查工作簿在工作表中是否存在
		/// </summary>
		public bool SheetExists(string shName)
		{

			foreach (var sh in GetSheets())
			{
				if (sh.Equals(shName, StringComparison.OrdinalIgnoreCase)) return true;
			}

			return false;
		}

		/// <summary>
		/// 检查工作簿中是否存在数据
		/// </summary>
		public bool IsEmptykSheet(string shName)
		{
			return GetSheetRows(shName) == 0;
		}

		/// <summary>
		/// 检查整个工作表中是否存在数据
		/// </summary>
		public bool IsEmptyWkBook()
		{
			var sheets = GetSheets();
			foreach (var sh in sheets)
			{
				if (!IsEmptykSheet(sh)) return false;
			}
			return true;
		}

		private string GetSheetName(string shName)
		{
			
			if(string.IsNullOrEmpty(shName)) throw new ArgumentException("工作表名称不能为空");
			var qName = shName;
			qName = qName.Trim(new char[] { '[',']','\'' });
			if (!qName.EndsWith("$")) qName = qName + "$";
			return qName;

		}

		/// <summary>
		/// 通过文件名后缀名称，取得ole连接字符串
		/// </summary>
		private string MkConnectionString(string location)
		{
			var connString = string.Empty;
			var extName = Path.GetExtension(Location);
			if (!string.IsNullOrEmpty(extName)) extName = extName.ToLower();

			switch (extName)
			{
				case ".xls":
					connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=NO;\"";
					break;
				case ".xlsx":
					connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=NO;\"";
					break;
				default:
					throw new ArgumentException(string.Format("文件{0}的格式不受支持", Location));
			}

			connString = string.Format(connString, Location);

			return connString;
		}

		public void Dispose()
		{
			Connection.Close();
		}
	}
}
